/**
 * 数据库操作模块
 */
import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import { v4 as uuidv4 } from 'uuid';
import path from 'path';
import fs from 'fs';
import {
    Conversation,
    SearchParams,
    ConversationStats,
    DbResult,
    AddConversationParams,
    UpdateConversationParams
} from './types.js';

// 数据库文件路径
const DB_DIR = path.join(process.env.HOME || process.env.USERPROFILE || '.', 'Documents', 'conversation_system');
const DB_PATH = path.join(DB_DIR, 'conversations.db');

// 确保数据库目录存在
if (!fs.existsSync(DB_DIR)) {
    fs.mkdirSync(DB_DIR, { recursive: true });
}

// 创建数据库连接
export async function getDb() {
    return open({
        filename: DB_PATH,
        driver: sqlite3.Database
    });
}

// 初始化数据库
export async function initDatabase(): Promise<DbResult> {
    try {
        const db = await getDb();

        // 创建对话表
        await db.exec(`
      CREATE TABLE IF NOT EXISTS conversations (
        id TEXT PRIMARY KEY,
        datetime TEXT NOT NULL,
        topic TEXT NOT NULL,
        keywords TEXT NOT NULL,
        summary TEXT NOT NULL,
        actions TEXT NOT NULL,
        files TEXT,
        status TEXT NOT NULL,
        content TEXT
      )
    `);

        // 创建索引
        await db.exec(`CREATE INDEX IF NOT EXISTS idx_conversations_datetime ON conversations(datetime)`);
        await db.exec(`CREATE INDEX IF NOT EXISTS idx_conversations_status ON conversations(status)`);

        return { success: true, message: '数据库初始化成功' };
    } catch (error) {
        console.error('数据库初始化失败:', error);
        return { success: false, message: `数据库初始化失败: ${error}` };
    }
}

// 生成对话ID
function generateConversationId(): string {
    // 获取当前最大ID
    return `CONV-${uuidv4().substring(0, 8).toUpperCase()}`;
}

// 添加对话记录
export async function addConversation(params: AddConversationParams): Promise<DbResult> {
    try {
        const db = await getDb();

        const id = generateConversationId();
        const datetime = new Date().toISOString();

        // 处理关键词
        const keywords = Array.isArray(params.keywords)
            ? params.keywords.join(',')
            : params.keywords;

        // 处理行动项
        const actions = Array.isArray(params.actions)
            ? params.actions.join(',')
            : params.actions;

        // 处理文件路径
        const files = params.files
            ? (Array.isArray(params.files) ? params.files.join(',') : params.files)
            : '';

        await db.run(
            `INSERT INTO conversations (id, datetime, topic, keywords, summary, actions, files, status, content)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [id, datetime, params.topic, keywords, params.summary, actions, files, params.status, params.content || '']
        );

        return {
            success: true,
            message: `对话记录添加成功，ID: ${id}`,
            data: { id, datetime }
        };
    } catch (error) {
        console.error('添加对话记录失败:', error);
        return { success: false, message: `添加对话记录失败: ${error}` };
    }
}

// 更新对话记录
export async function updateConversation(params: UpdateConversationParams): Promise<DbResult> {
    try {
        const db = await getDb();

        // 检查对话是否存在
        const conversation = await db.get('SELECT id FROM conversations WHERE id = ?', params.id);
        if (!conversation) {
            return { success: false, message: `对话记录不存在: ${params.id}` };
        }

        // 构建更新语句
        const updates: string[] = [];
        const values: any[] = [];

        if (params.topic !== undefined) {
            updates.push('topic = ?');
            values.push(params.topic);
        }

        if (params.keywords !== undefined) {
            const keywords = Array.isArray(params.keywords)
                ? params.keywords.join(',')
                : params.keywords;
            updates.push('keywords = ?');
            values.push(keywords);
        }

        if (params.summary !== undefined) {
            updates.push('summary = ?');
            values.push(params.summary);
        }

        if (params.actions !== undefined) {
            const actions = Array.isArray(params.actions)
                ? params.actions.join(',')
                : params.actions;
            updates.push('actions = ?');
            values.push(actions);
        }

        if (params.files !== undefined) {
            const files = Array.isArray(params.files)
                ? params.files.join(',')
                : params.files;
            updates.push('files = ?');
            values.push(files);
        }

        if (params.status !== undefined) {
            updates.push('status = ?');
            values.push(params.status);
        }

        if (params.content !== undefined) {
            updates.push('content = ?');
            values.push(params.content);
        }

        if (updates.length === 0) {
            return { success: false, message: '没有提供要更新的字段' };
        }

        // 添加ID到值数组
        values.push(params.id);

        // 执行更新
        await db.run(
            `UPDATE conversations SET ${updates.join(', ')} WHERE id = ?`,
            values
        );

        return { success: true, message: `对话记录更新成功: ${params.id}` };
    } catch (error) {
        console.error('更新对话记录失败:', error);
        return { success: false, message: `更新对话记录失败: ${error}` };
    }
}

// 删除对话记录
export async function deleteConversation(id: string): Promise<DbResult> {
    try {
        const db = await getDb();

        // 检查对话是否存在
        const conversation = await db.get('SELECT id FROM conversations WHERE id = ?', id);
        if (!conversation) {
            return { success: false, message: `对话记录不存在: ${id}` };
        }

        // 执行删除
        await db.run('DELETE FROM conversations WHERE id = ?', id);

        return { success: true, message: `对话记录删除成功: ${id}` };
    } catch (error) {
        console.error('删除对话记录失败:', error);
        return { success: false, message: `删除对话记录失败: ${error}` };
    }
}

// 获取对话记录
export async function getConversation(id: string): Promise<DbResult> {
    try {
        const db = await getDb();

        const conversation = await db.get('SELECT * FROM conversations WHERE id = ?', id);
        if (!conversation) {
            return { success: false, message: `对话记录不存在: ${id}` };
        }

        // 处理数据格式
        const result: Conversation = {
            id: conversation.id,
            datetime: conversation.datetime,
            topic: conversation.topic,
            keywords: conversation.keywords.split(','),
            summary: conversation.summary,
            actions: conversation.actions.split(','),
            files: conversation.files ? conversation.files.split(',') : [],
            status: conversation.status,
            content: conversation.content
        };

        return { success: true, data: result };
    } catch (error) {
        console.error('获取对话记录失败:', error);
        return { success: false, message: `获取对话记录失败: ${error}` };
    }
}

// 搜索对话记录
export async function searchConversations(params: SearchParams): Promise<DbResult> {
    try {
        const db = await getDb();

        const conditions: string[] = [];
        const values: any[] = [];

        if (params.id) {
            conditions.push('id = ?');
            values.push(params.id);
        }

        if (params.topic) {
            conditions.push('topic LIKE ?');
            values.push(`%${params.topic}%`);
        }

        if (params.keywords && params.keywords.length > 0) {
            const keywordConditions = params.keywords.map(() => 'keywords LIKE ?');
            conditions.push(`(${keywordConditions.join(' OR ')})`);
            params.keywords.forEach(keyword => values.push(`%${keyword}%`));
        }

        if (params.status) {
            conditions.push('status = ?');
            values.push(params.status);
        }

        if (params.date) {
            conditions.push('datetime LIKE ?');
            values.push(`${params.date}%`);
        }

        const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
        const limitClause = params.limit ? `LIMIT ${params.limit}` : '';

        const query = `
      SELECT * FROM conversations
      ${whereClause}
      ORDER BY datetime DESC
      ${limitClause}
    `;

        const rows = await db.all(query, values);

        // 处理数据格式
        const results: Conversation[] = rows.map(row => ({
            id: row.id,
            datetime: row.datetime,
            topic: row.topic,
            keywords: row.keywords.split(','),
            summary: row.summary,
            actions: row.actions.split(','),
            files: row.files ? row.files.split(',') : [],
            status: row.status,
            content: row.content
        }));

        return { success: true, data: results };
    } catch (error) {
        console.error('搜索对话记录失败:', error);
        return { success: false, message: `搜索对话记录失败: ${error}` };
    }
}

// 获取对话统计信息
export async function getConversationStats(): Promise<DbResult> {
    try {
        const db = await getDb();

        // 获取总对话数
        const totalCountResult = await db.get('SELECT COUNT(*) as count FROM conversations');
        const totalCount = totalCountResult.count;

        // 按状态统计
        const statusCountsResult = await db.all('SELECT status, COUNT(*) as count FROM conversations GROUP BY status');
        const statusCounts: Record<string, number> = {};
        statusCountsResult.forEach((row: any) => {
            statusCounts[row.status] = row.count;
        });

        // 获取最近的对话
        const recentConversationsResult = await db.all(
            'SELECT * FROM conversations ORDER BY datetime DESC LIMIT 5'
        );
        const recentConversations: Conversation[] = recentConversationsResult.map((row: any) => ({
            id: row.id,
            datetime: row.datetime,
            topic: row.topic,
            keywords: row.keywords.split(','),
            summary: row.summary,
            actions: row.actions.split(','),
            files: row.files ? row.files.split(',') : [],
            status: row.status
        }));

        // 获取常用关键词
        const allKeywords = await db.all('SELECT keywords FROM conversations');
        const keywordCounts: Record<string, number> = {};

        allKeywords.forEach((row: any) => {
            const keywords = row.keywords.split(',');
            keywords.forEach((keyword: string) => {
                const trimmedKeyword = keyword.trim();
                if (trimmedKeyword) {
                    keywordCounts[trimmedKeyword] = (keywordCounts[trimmedKeyword] || 0) + 1;
                }
            });
        });

        const topKeywords = Object.entries(keywordCounts)
            .map(([keyword, count]) => ({ keyword, count: count as number }))
            .sort((a, b) => b.count - a.count)
            .slice(0, 10);

        const stats: ConversationStats = {
            totalCount,
            statusCounts,
            recentConversations,
            topKeywords
        };

        return { success: true, data: stats };
    } catch (error) {
        console.error('获取对话统计信息失败:', error);
        return { success: false, message: `获取对话统计信息失败: ${error}` };
    }
}